package com.tyl.dao.bill;

import com.tyl.dao.BaseDao;
import com.tyl.pojo.Bill;
import javafx.scene.layout.Background;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BillDaoImpl implements BillDao{
    /**
     * 获取订单id
     * **/
    public int getBillCountByProviderId(Connection connection, String providerId) throws SQLException {
        PreparedStatement pstm =null;
        ResultSet rs=null;
        int count=0;
        if (connection!=null){
            String sql="select count(1) as billCount from smbms_bill where providerId = ?";
            Object[]param={providerId};
            rs= BaseDao.execute(connection, pstm, sql, rs, param);
            if (rs.next()){
                count=rs.getInt("billCount");
            }
        }
        BaseDao.closeResource(null,pstm,rs);
        return count;
    }
    /**
     * 获取订单列表
     * **/
    public List<Bill> getBillList(Connection connection, Bill bill) throws SQLException {
        PreparedStatement psmt=null;
        StringBuilder sql = new StringBuilder();//getProductName   getProviderId  getIsPayment
         ArrayList<Bill> billList = new ArrayList<Bill>();
        ResultSet rs =null;
        if (connection!=null){
            sql.append("select b.*,p.proName as providerName from smbms_bill b, smbms_provider p where b.providerId = p.id");
            ArrayList<Object> list = new ArrayList<Object>();
            if (bill.getProductName()!=null&&!bill.getProductName().equals("")){
                sql.append(" and ProductName like ?");
                list.add("%"+bill.getProductName()+"%");
            }
            if (bill.getProviderId()>0){
                sql.append(" and ProviderId =?");
                list.add(bill.getProviderId());
            }
            if (bill.getIsPayment()>0){
                sql.append(" and IsPayment =?");
                list.add(bill.getIsPayment());
            }
            Object[]params=list.toArray();
            rs = BaseDao.execute(connection, psmt, sql.toString(), rs, params);
            while (rs.next()){
                Bill _bill=new Bill();
                _bill.setId(rs.getInt("id"));
                _bill.setBillCode(rs.getString("billCode"));
                _bill.setProductName(rs.getString("productName"));
                _bill.setProductDesc(rs.getString("productDesc"));
                _bill.setProductUnit(rs.getString("productUnit"));
                _bill.setProductCount(rs.getBigDecimal("productCount"));
                _bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                _bill.setIsPayment(rs.getInt("isPayment"));
                _bill.setProviderId(rs.getInt("providerId"));
                _bill.setProviderName(rs.getString("providerName"));
                _bill.setCreationDate(rs.getTimestamp("creationDate"));
                _bill.setCreatedBy(rs.getInt("createdBy"));
                billList.add(_bill);
            }
            BaseDao.closeResource(null,null,rs);
        }
        return billList;
    }
    /**
     * 获取订单id
     * **/
    public Bill getBillId(Connection connection, String billId) throws SQLException {
        PreparedStatement psmt=null;
        Bill bill = null;
        ResultSet rs=null;
        if (connection!=null){
            //String sql="select *from smbms_bill where id=?";
            String sql="select b.*,p.proName as providerName from smbms_bill b, smbms_provider p where b.providerId = p.id and b.id=?";
            Object []params={billId};
            rs = BaseDao.execute(connection, psmt, sql, rs, params);
            while (rs.next()){
                bill = new Bill();
                bill.setId(rs.getInt("id"));
                bill.setBillCode(rs.getString("billCode"));
                bill.setProductName(rs.getString("productName"));
                bill.setProductDesc(rs.getString("productDesc"));
                bill.setProductUnit(rs.getString("productUnit"));
                bill.setProductCount(rs.getBigDecimal("productCount"));
                bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                bill.setIsPayment(rs.getInt("isPayment"));
                bill.setProviderId(rs.getInt("providerId"));
                bill.setProviderName(rs.getString("providerName"));
                bill.setModifyBy(rs.getInt("modifyBy"));
                bill.setModifyDate(rs.getTimestamp("modifyDate"));
            }
            BaseDao.closeResource(null,psmt,rs);
        }
        return bill;
    }

    /**
     * 删除订单
     * **/
    public int deleteBill(Connection connection, String billId) throws SQLException {
        PreparedStatement psmt=null;
        int flag=0;
        if (connection!=null){
            String sql="delete from smbms_bill where smbms_bill.id=?";
            Object []params={billId};
            flag = BaseDao.execute(connection, psmt, sql, params);
        }
        BaseDao.closeResource(null,psmt,null);
        return flag;
    }

    /**
     * 添加订单
     * **/
    public int addBill(Connection connection, Bill bill) throws SQLException {
        PreparedStatement psmt=null;
        int flag=0;
        if (connection!=null){
            String sql = "insert into smbms_bill (billCode,productName,productDesc," +
                    "productUnit,productCount,totalPrice,isPayment,providerId,createdBy,creationDate) " +
                    "values(?,?,?,?,?,?,?,?,?,?)";
            Object[] params = {bill.getBillCode(), bill.getProductName(), bill.getProductDesc(),
                    bill.getProductUnit(), bill.getProductCount(), bill.getTotalPrice(), bill.getIsPayment(),
                    bill.getProviderId(), bill.getCreatedBy(), bill.getCreationDate()};
             flag = BaseDao.execute(connection, psmt, sql,params);
        }
        BaseDao.closeResource(null,psmt,null);
        return flag;
    }

    /**
     * 修改订单
     * **/
    public int updateBill(Connection connection, Bill bill) throws SQLException {
        int flag = 0;
        PreparedStatement pstm = null;
        if (null != connection) {
            String sql = "update smbms_bill set productName=?," +
                    "productDesc=?,productUnit=?,productCount=?,totalPrice=?," +
                    "isPayment=?,providerId=?,modifyBy=?,modifyDate=? where id = ? ";
            Object[] params = {bill.getProductName(), bill.getProductDesc(),
                    bill.getProductUnit(), bill.getProductCount(), bill.getTotalPrice(), bill.getIsPayment(),
                    bill.getProviderId(), bill.getModifyBy(), bill.getModifyDate(), bill.getId()};
            flag = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return flag;
    }
}
